<?php

namespace app\admin\controller;

use app\common\controller\Backend;
use think\Db;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Reader\Xlsx as XlsxReader;
use PhpOffice\PhpSpreadsheet\Reader\Xls as XlsReader;
use PhpOffice\PhpSpreadsheet\Reader\Csv as CsvReader;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx as XlsxWrite;
/**
 * @icon fa fa-circle-o
 */
class Product extends Backend {
	
	/**
	 * Product模型对象
	 * 
	 * @var \app\admin\model\Product
	 */
	protected $model = null;
	public function _initialize() {
		parent::_initialize ();
		$this->model = new \app\admin\model\Product ();
	}
	public function import() {
		ini_set ( 'memory_limit', '1024M' );
		$file = $this->request->request ( 'file' );
		if (! $file) {
			$this->error ( __ ( 'Parameter %s can not be empty', 'file' ) );
		}
		$filePath = ROOT_PATH . DS . 'public' . DS . $file;
		if (! is_file ( $filePath )) {
			$this->error ( __ ( 'No results were found' ) );
		}
		$PHPReader = new XlsxReader();
		if (! $PHPReader->canRead ( $filePath )) {
			$PHPReader = new XlsReader;
			if (! $PHPReader->canRead ( $filePath )) {
				$PHPReader = new CsvReader ();
				if (! $PHPReader->canRead ( $filePath )) {
					$this->error ( __ ( 'Unknown data format' ) );
				}
			}
		}
		
		// 导入文件首行类型,默认是注释,如果需要使用字段名称请使用name
		$importHeadType = isset ( $this->importHeadType ) ? $this->importHeadType : 'comment';
		
		$table = $this->model->getQuery ()->getTable ();
		$database = \think\Config::get ( 'database.database' );
		$fieldArr = [ ];
		$list = db ()->query ( "SELECT COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [ 
				$table,
				$database 
		] );
		foreach ( $list as $k => $v ) {
			if ($importHeadType == 'comment') {
				$fieldArr [$v ['COLUMN_COMMENT']] = [$v['COLUMN_NAME'],$v['DATA_TYPE']];
			} else {
				$fieldArr [$v ['COLUMN_NAME']] = [$v['COLUMN_NAME'],$v['DATA_TYPE']];
			}
		}
		$PHPExcel = $PHPReader->load ( $filePath ); // 加载文件
		$currentSheet = $PHPExcel->getSheet ( 0 ); // 读取文件中的第一个工作表
		$allColumn = $currentSheet->getHighestDataColumn (); // 取得最大的列号
		$allRow = $currentSheet->getHighestRow (); // 取得一共有多少行
		$maxColumnNumber = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($allColumn);
		for($currentRow = 1; $currentRow <= 1; $currentRow ++) {
			for($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn ++) {
				$val = $currentSheet->getCellByColumnAndRow ( $currentColumn, $currentRow )->getValue ();
				$fields [] = $val;
			}
		}
		$insert = [ ];
		for($currentRow = 2; $currentRow <= $allRow; $currentRow ++) {
			$values = [ ];
			$flag = 0;
			for($currentColumn = 1; $currentColumn <= $maxColumnNumber; $currentColumn ++) {
				$val = $currentSheet->getCellByColumnAndRow ( $currentColumn, $currentRow )->getFormattedValue();
				$values [] = is_null ( $val ) ? null : $val;
				if($val != ''){
					$flag = 1;
				}
			}
			if(!$flag){
				break;
			}
			$row = [ ];
			$temp = array_combine ( $fields, $values );
			foreach ( $temp as $k => $v ) {
				if (isset ( $fieldArr [$k] )) {
					if($fieldArr [$k][1] == 'date'){
						$row [$fieldArr [$k][0]] = date('Y-m-d',strtotime($v));
					}elseif ($fieldArr [$k][1] == 'datetime'){
						$row [$fieldArr [$k][0]] = date('Y-m-d H:i:s',strtotime($v));
					}
					else{
						$row [$fieldArr [$k][0]] = $v;
					}
				}
			}
			$row['add_time'] = date('Y-m-d H:i:s');
			unset($row['id']);
			if ($row) {
				$insert [] = $row;
			}
		}
		if (! $insert) {
			$this->error ( __ ( 'No rows were updated' ) );
		}
		try {
			$this->model->saveAll ( $insert );
		} catch ( \think\exception\PDOException $exception ) {
			$this->error ( $exception->getMessage () );
		} catch ( \Exception $e ) {
			$this->error ( $e->getMessage () );
		}
		
		$this->success ();
	}

	public function export(){
		set_time_limit(0);
		if ($this->request->isPost()) {
            $search = $this->request->post('search');
            $ids = $this->request->post('ids');
            $filter = $this->request->post('filter');
            $op = $this->request->post('op');
			// $columns = $this->request->post('columns');
			$columns = 'id,purchase_date,purchase_num,first_cat,second_cat,code,sku,product_code,product_name_cn,product_name_en,brand,spec,case_quantity,shelf_life,produce_date,best_by_date,purchase_price,online_price,vip_price,effect,product_mark,weight,from,ingredients,units,shui_code,hs_code,company_code,storehouse_code,com_sku,customs_clearance_mode,supplier,legal_quantity,second_quantity';
			$whereIds = $ids == 'all' ? '1=1' : ['id' => ['in', explode(',', $ids)]];
            $this->request->get(['search' => $search, 'ids' => $ids, 'filter' => $filter, 'op' => $op]);
			list($where, $sort, $order, $offset, $limit) = $this->buildparams();
			$line = 1;
			$list = [];
			$this->model
                ->field($columns)
                ->where($where)
                ->where($whereIds)->chunk(100,function($items)use(&$list){
					$list = array_merge($list,collection($items)->toArray());
				});
			$spreadsheet = new Spreadsheet();
			$sheet = $spreadsheet->getActiveSheet();
			$table = $this->model->getQuery ()->getTable ();
			$database = \think\Config::get ( 'database.database' );
			$fieldArr = [];
			$column_list = db ()->query ( "SELECT COLUMN_NAME,COLUMN_COMMENT,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ? AND TABLE_SCHEMA = ?", [ 
					$table,
					$database 
			] );
			$number_col = [];
			foreach ( $column_list as $k => $v ) {
				if($v['COLUMN_NAME'] != 'product_img' && $v['COLUMN_NAME'] != 'add_time'){
					$fieldArr [$v['COLUMN_NAME']] = $v['COLUMN_COMMENT'];
					$sheet->getColumnDimensionByColumn($k)->setAutoSize(true);
					if(strstr($v['DATA_TYPE'] , 'int')){
						$number_col[] = $sheet->getColumnDimensionByColumn($k)->getColumnIndex();
					}
				}
			}
			array_unshift($list,$fieldArr);
			$sheet->getStyle('H1:H' .(count($list)+1))->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);
			$sheet->setTitle('templete');
			$sheet->fromArray($list);
			$fileName = date('YmdHis') . rand(101,999) .'.xlsx';
			header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');  
			header("Content-Disposition: attachment;filename='{$fileName}'");  
			header('Cache-Control: max-age=0');
			ob_end_clean(); 
			ob_start(); 
			$writer = new XlsxWrite($spreadsheet);
			$writer->save('php://output');
            return;
        }
	}
}
